
project , uses(../data_output/legislators_klarner.dta)


	**********
	** MISSOURI house
	** Life-time term limites: 4 x 2-year terms.
	** First year: 2002	
	**********	
	use ../data_output/legislators_klarner.dta, clear
	tostring dist, generate(D)
	keep if state=="MO" & chamber=="house"
	keep if regexm(term, "1995|1997|1999|2001|2003|2005|2007|2009|2011|2013|2015")
	gen lastname = trim(regexs(1)) if regexm(lower(CandName),"([^,]+)")
	gen firstname = trim(regexs(1)) if regexm(lower(CandName),",( [^ ]+)")	
	gen mergename =  lastname //firstname + " " +
	bys  mergename term: gen n = _n
	by  mergename term: egen mean_n=mean(n)
	replace mergename = lastname + "("+ D +")"  if mean_n>1  //firstname  + " " +  lastname //firstname+ ". "
	bys lastname : tab  term if mean_n>1
	drop n mean_n
	save ../data_output/legislators_MO_house_1995_2016.dta, replace

	gen e_won = elections_won
	qui forval i= 1960(2)1992{ //years enacted
		replace e_won = subinstr(e_won, "`i';", "",.)
	}
	tostring year, gen(regexp)
	replace regexp = "^([^!]+"+regexp+";)"
	gen previous_wins = regexs(1) if regexm(e_won, regexp)
	egen prev_terms = noccur(previous_wins) , string(";")
	gen termlimited=prev_terms==4
	save ../data_output/legislators_MO_house_1995_2016.dta, replace
project, creates(../data_output/legislators_MO_house_1995_2016.dta)	

project , original(../data_input/MO/mo_bills.csv)
	import delimited using ../data_input/MO/mo_bills.csv,  clear  delimiters(";") bindquotes(strict) encoding("utf-8")	stringcols(_all) 
	keep if regexm(year, "1995|1996|1997|1998|1999")
	tempfile tmp
	save `tmp'	
	
	foreach year in 2000 2001 2002 2003  { 
project, original(../data_input/MO/mo_bills_`year'.csv)	
		import delimited using ../data_input/MO/mo_bills_`year'.csv,  clear  delimiters(";") bindquotes(strict) encoding("utf-8")  stringcols(_all) 
		append using `tmp'
		save `tmp', replace
	}
	
	foreach year in 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013  2014 2015 2016 { 
project, original(../data_input/MO/mo_bills_`year'.csv)	
		import delimited using ../data_input/MO/mo_bills_`year'.csv,  clear  delimiters(";") bindquotes(strict) encoding("utf-8")  stringcols(_all) 
		gen x = regexs(1) if regexm( fiscal_text , "Total Estimated Net Effect(.*)"  )
		foreach i in less more greater than up to unknown or {
			replace x = subinstr(lower(x), "`i'", "", .)
		}
		gen y = trim( regexs(1)) if regexm( lower(x) , "fund.?([ $,0-9()]+)"  )
		replace y = regexr(y, "\s\s+", "\s" )
		split y, generate(net_effect)
		qui foreach v of varlist net_effect* {
			replace `v' = subinstr(`v', "($", "-",.)
			replace `v' = subinstr(`v', "$", "",.)
			replace `v' = subinstr(`v', ",", "",.)
			replace `v' = "-" + subinstr(`v', ")", "",.) if regexm(`v', "[)]") & regexm(`v', "-")==0
			replace `v' = subinstr(`v', ")", "",.)
			replace `v' = "." if regexm(`v', "[0-9]")==0
			replace `v' = "0" if `v' == "."
		}
		destring net_effect*, replace
		replace net_effect1 = 0 if net_effect1==.
	
		egen fiscal_effect = rowmean(net_effect* ) if type=="sponsor"
		append using `tmp'
		save `tmp', replace
	}

	
	gen inoffice = year 
	drop year
	gen term = ""
	replace term = "1991-1992" if regexm(inoffice, "1991|1992")
	replace term = "1993-1994" if regexm(inoffice, "1993|1994")
	replace term = "1995-1996" if regexm(inoffice, "1995|1996")	
	replace term = "1997-1998" if regexm(inoffice, "1997|1998")
	replace term = "1999-2000" if regexm(inoffice, "1999|2000")
	replace term = "2001-2002" if regexm(inoffice, "2001|2002")
	replace term = "2003-2004" if regexm(inoffice, "2003|2004")
	replace term = "2005-2006" if regexm(inoffice, "2005|2006")
	replace term = "2007-2008" if regexm(inoffice, "2007|2008")
	replace term = "2009-2010" if regexm(inoffice, "2009|2010")
	replace term = "2011-2012" if regexm(inoffice, "2011|2012")
	replace term = "2013-2014" if regexm(inoffice, "2013|2014")
	replace term = "2015-2016" if regexm(inoffice, "2015|2016")	
	
	replace name =  subinstr(name, " ","",.)  
	replace name =  subinstr(name, "'","",.)  
	replace name =  subinstr(name, "-","",.)  
	gen mergename = ""
	format %24s mergename name	
	order mergename name	
	replace mergename = lower(trim(regexs(1))) if regexm(lower(name),"([^,( ]+)")
	replace mergename =  subinstr(mergename, "-","",.)
	replace mergename = name if regexm(mergename, "^baker") & regexm(term, "2005|2007")
	replace mergename = name if regexm(mergename, "^brown") & regexm(term, "2005|2007|2009|2011|2013|2015")
	replace mergename = name if regexm(mergename, "^conway") & regexm(term, "2011|2013|2015")
	replace mergename = name if regexm(mergename, "^cooper") & regexm(term, "2003|2005|2007")
	replace mergename = name if regexm(mergename, "^cunningham") & regexm(term, "2003|2005|2007")
	replace mergename = name if regexm(mergename, "^davis") & regexm(term, "1997|1999|2003")
	replace mergename = name if regexm(mergename, "^fitzwater") & regexm(term, "2015")
	replace mergename = name if regexm(mergename, "^graham") & regexm(term, "1997|1999")
	replace mergename = name if regexm(mergename, "^green") & regexm(term, "2001")
	replace mergename = name if regexm(mergename, "^harris") & regexm(term, "2003|2005|2007")
	replace mergename = name if regexm(mergename, "^hartzler") & regexm(term, "1995|1997|1999")
	replace mergename = name if regexm(mergename, "^hoskins") & regexm(term, "2009")
	replace mergename = name if regexm(mergename, "^johnson") & regexm(term, "2001|2003|2005")
	replace mergename = name if regexm(mergename, "^jones") & regexm(term, "2007|2009|2011|2013")
	replace mergename = name if regexm(mergename, "^kelly") & regexm(term, "2001|2003")
	replace mergename = name if regexm(mergename, "^marshall") & regexm(term, "1995")
	replace mergename = name if regexm(mergename, "^quinn") & regexm(term, "2007")
	replace mergename = name if regexm(mergename, "^smith") & regexm(term, "2003|2005|2007|2009|2011|2013")
	replace mergename = name if regexm(mergename, "^thompson") & regexm(term, "1999")
	replace mergename = name if regexm(mergename, "^williams") & regexm(term, "1995|1997|1999")	
	replace mergename = name if regexm(mergename, "^wilson") & regexm(term, "2001|2003|2005|2007|2009")
	replace mergename = name if regexm(mergename, "^wright") & regexm(term, "2005")
	replace mergename = lower(regexr(mergename, ",[^(]+\(","("))
	replace mergename = lower(regexr(mergename, "\(0+","("))
	replace mergename = "graham(24)" if regexm(mergename, "graham,chuck") & regexm(term, "1997|1999")
	replace mergename = "graham(106)" if regexm(mergename, "graham,jamese|graham,jim") & regexm(term, "1997|1999")
	replace mergename = "gray" if regexm(mergename, "waltongray") & regexm(term, "2009|2011|2013|2015")
	replace mergename = "hartzler(123)" if regexm(mergename, "hartzler,ed") & regexm(term, "1995|1997")
	replace mergename = "hartzler(124)" if regexm(mergename, "hartzler,vicky") & regexm(term, "1995|1997")
	replace mergename = "marshall(133)" if regexm(mergename, "marshall,williamg.") & regexm(term, "1995")
	replace mergename = "marshall(26)" if regexm(mergename, "marshall,thomasw.") & regexm(term, "1995")
	replace mergename = "saintonge" if regexm(mergename, "st.onge") & regexm(term, "2001|2003|2005|2007")
	replace mergename = "thompson(37)" if regexm(mergename, "thompson,vernon") & regexm(term, "1999")
	replace mergename = "williams(121)" if regexm(mergename, "williams,deleta") & regexm(term, "1995|1997|1999")
	replace mergename = "williams(159)" if regexm(mergename, "williams,marilyna.") & regexm(term, "1995|1997|1999")
	replace mergename = "pierson" if regexm(mergename, "piersonsr.") & regexm(term, "2015")
	replace mergename = "davis(63)" if regexm(mergename, "davis,dorathea") & regexm(term, "1997|1999")
	replace mergename = "davis(122)" if regexm(mergename, "davis,d.j.") & regexm(term, "1997|1999")
	replace mergename = "edwards" if regexm(mergename, "edwardspavia") & regexm(term, "1995|1997")
	replace mergename = "sandersbrooks" if regexm(mergename, "brooks") & regexm(term, "2001|2003|2005")
	replace mergename = regexr(mergename, "^and","") if regexm(mergename, "and[bcdhklmpstvw]")
	
	
	merge m:1  mergename term  using ../data_output/legislators_MO_house_1995_2016.dta
			
	*38 legislators do not appear in the master data: 
	drop if _merge==1
	replace fiscal_effect = 0 if _merge==2
	gen law = 0 if type=="sponsor"
	replace law = 1 if regexm(lower(status), "delivered to secretary of state|adopted|voted do pass")	& type=="sponsor"
	gen sponsor_approp = regexm( lower(history) , "budget|approp") 
	replace sponsor_approp = . if  history==""
	
	save ../data_output/bills_by_sponsor_MO_1995_2016.dta, replace
project , creates(../data_output/bills_by_sponsor_MO_1995_2016.dta)
	
	
	use ../data_output/bills_by_sponsor_MO_1995_2016.dta, clear
	gen sponsor = 1 if  type=="sponsor"
	gen cosponsor = 1 if  type=="cosponsor"
	gen billlength = length(billtext)
	gen fiscal_analysis = fiscal_link!="" if  type=="sponsor"
	gen fiscal_positive = fiscal_effect>0 if  type=="sponsor"
	gen fiscal_negative = fiscal_effect<0 if  type=="sponsor"
	gen fiscal_neutral = fiscal_effect==0 if  type=="sponsor"
	gen law_fiscal_effect = 0 if fiscal_effect!=. & type=="sponsor"
	replace law_fiscal_effect = fiscal_effect if law==1 & type=="sponsor"
	order fiscal_effect law* term
	collapse (sum) fiscal_analysis fiscal_positive fiscal_negative fiscal_neutral law *sponsor* billlength total_fiscal_effect = fiscal_effect (mean) law_fiscal_effect mean_fiscal_effect = fiscal_effect (first) elections_* terms_in_office win TermLengthActual CandName district chamber state party mergename inoffice year termlimited vtsh, by(CandId term )
	
	foreach v of varlist sponsor_approp billlength cosponsor law total_fiscal_effect mean_fiscal_effect fiscal_analysis fiscal_positive fiscal_negative fiscal_neutral {
	bys  term: egen max = max(`v')
		replace `v' = . if max==0
		drop max
	}
	save ../data_output/total_bills_by_sponsor_MO_1995_2016.dta, replace
project , creates(../data_output/total_bills_by_sponsor_MO_1995_2016.dta)
	
	      *    MO house 2015-2016, MO house 2007-2008, MO house 2003-2004
	

	**MO voting
	
	use ../data_output/legislators_klarner.dta, clear
	gen lastname = regexs(1) if regexm(lower(CandName),"([^,]+), [^!]")
	gen firstname = regexs(1) if regexm(lower(CandName),"[^,]+, ([^ ]+)")
	keep if state=="MO" & chamber=="house"
	keep if regexm(term, "1999|2001|2003|2005|2007|2009|2011|2013|2015")  // 
	gen os_mergename = lastname
	bys  os_mergename term: gen n = _n
	by  os_mergename term: egen mean_n=mean(n)
	tostring district, gen(district_string)
	replace os_mergename = lastname + district_string  if mean_n>1  
	drop n mean_n
	tempfile tmp
	save `tmp', replace
	
project, original(../data_input/MO/mo_votes.csv)	
	import delimited using ../data_input/MO/mo_votes.csv,  clear  delimiters(";") bindquotes(strict) encoding("utf-8") varnames(1)
	rename house chamber
	keep if chamber=="house"
	gen term = ""
	replace term = "1999-2000" if regexm(date, "1999|2000")
	replace term = "2001-2002" if regexm(date, "2001|2002")
	replace term = "2003-2004" if regexm(date, "2003|2004")
	replace term = "2005-2006" if regexm(date, "2005|2006")
	replace term = "2007-2008" if regexm(date, "2007|2008")
	replace term = "2009-2010" if regexm(date, "2009|2010")
	replace term = "2011-2012" if regexm(date, "2011|2012")
	replace term = "2013-2014" if regexm(date, "2013|2014")
	replace term = "2015-2016" if regexm(date, "2015|2016")
	gen os_mergename = trim(lower(name))
	replace os_mergename = lower(os_mergename)
	replace os_mergename = subinstr(os_mergename,"’","",.)
	replace os_mergename = subinstr(os_mergename,"'","",.)
	replace os_mergename = subinstr(os_mergename,"-","",.)
	replace os_mergename = subinstr(os_mergename," ","",.)
	
	
	replace os_mergename = "barry"	if os_mergename=="barry100" & regexm(term, "2000|2002")
	replace os_mergename = "bartle"	if os_mergename=="bartle28" & regexm(term, "2000")
	replace os_mergename = "boucher"	if os_mergename=="boucher48" & regexm(term, "2000")
	replace os_mergename = "bray"	if os_mergename=="bray84" & regexm(term, "2000|2002")
	replace os_mergename = "kelley"	if os_mergename=="kelley47" & regexm(term, "2000|2002")
	replace os_mergename = "kelly"	if os_mergename=="kelly27" & regexm(term, "2000")
	replace os_mergename = "may"	if os_mergename=="may108" & regexm(term, "2000")
	replace os_mergename = "mays"	if os_mergename=="mays50" & regexm(term, "2000|2002")
	replace os_mergename = "pouche"	if os_mergename=="pouche30" & regexm(term, "2000")
	replace os_mergename = "thompson"	if os_mergename=="thompson37" & regexm(term, "2000")
	replace os_mergename = "vanzandt"	if os_mergename=="zandt" & regexm(term, "2000|2002")
	replace os_mergename = "wilson"	if os_mergename=="wilson25" & regexm(term, "2000")	
	replace os_mergename = "may"	if os_mergename=="may149" & regexm(term, "2002|2004")
	replace os_mergename = "saintonge"	if os_mergename=="onge" & regexm(term, "2002|2004|2006")
	replace os_mergename = "sandersbrooks"	if os_mergename=="brooks" & regexm(term, "2002|2004|2006")
	replace os_mergename = "icet"	if os_mergename=="\icet" & regexm(term, "2004")
	replace os_mergename = "lembke"	if os_mergename=="lembke85" & regexm(term, "2004")
	replace os_mergename = "lipke"	if os_mergename=="lipke157" & regexm(term, "2004")
	replace os_mergename = "mayer"	if os_mergename=="mayer159" & regexm(term, "2004")
	replace os_mergename = "hanaway"	if os_mergename=="speaker" & regexm(term, "2004")
	replace os_mergename = "kreider"	if os_mergename=="speaker" & regexm(term, "2002")
	replace os_mergename = "gaw"	if os_mergename=="speaker" & regexm(term, "2000")
	replace os_mergename = "jetton"	if os_mergename=="speaker" & regexm(term, "2006")
	replace os_mergename = "day"	if os_mergename=="days" & regexm(term, "2006")
	replace os_mergename = "jones"	if os_mergename=="jones117" & regexm(term, "2006")
	replace os_mergename = "jones"	if os_mergename=="jones117" & regexm(term, "2006")
	replace os_mergename = "wrightjones"	if os_mergename=="wrightjones63" & regexm(term, "2006")
	replace os_mergename = "thompson"	if os_mergename=="thompson37" & regexm(term, "2000")
	
	replace os_mergename = "day"	if os_mergename=="days" & regexm(term, "2008|2010")
	replace os_mergename = "emery"	if os_mergename=="emery867" & regexm(term, "2008")
	replace os_mergename = "ervin"	if os_mergename=="ervin1520" & regexm(term, "2008")
	replace os_mergename = "grisamore"	if os_mergename=="grisamore129" & regexm(term, "2008")
	replace os_mergename = "harris110"	if os_mergename=="harris110184" & regexm(term, "2008")
	replace os_mergename = "hoskins"	if os_mergename=="hoskins1712" & regexm(term, "2008")
	replace os_mergename = "hughes"	if os_mergename=="hughes33" & regexm(term, "2008")
	replace os_mergename = "hunter"	if os_mergename=="hunter489" & regexm(term, "2008")
	replace os_mergename = "jones117"	if os_mergename=="jones117921" & regexm(term, "2008")
	replace os_mergename = "kraus"	if os_mergename=="kraus232" & regexm(term, "2008")
	replace os_mergename = "liese"	if os_mergename=="liese667" & regexm(term, "2008")
	replace os_mergename = "marsh"	if os_mergename=="marsh328" & regexm(term, "2008")
	replace os_mergename = "meiners"	if os_mergename=="meiners301" & regexm(term, "2008")
	replace os_mergename = "muschany"	if os_mergename=="muschany315" & regexm(term, "2008")
	replace os_mergename = "muschany"	if os_mergename=="muschany99" & regexm(term, "2008")
	replace os_mergename = "chappellenadal"	if os_mergename=="chappelle" & regexm(term, "2008|2010")
	replace os_mergename = "nance"	if os_mergename=="nance120" & regexm(term, "2008")
	replace os_mergename = "nieves"	if os_mergename=="nieves107" & regexm(term, "2008")
	replace os_mergename = "leblanc"	if os_mergename=="blanc" & regexm(term, "2010")
	replace os_mergename = "levota"	if os_mergename=="vota" & regexm(term, "2010")
	replace os_mergename = "mcghee"	if os_mergename=="ghee" & regexm(term, "2008|2010|2012")
	replace os_mergename = "mcneil"	if os_mergename=="neil" & regexm(term, "2010|2012|2014|2016")
	replace os_mergename = "mcdonald"	if os_mergename=="donald" & regexm(term, "2010|2012|2014|2016")
	replace os_mergename = "mcnary"	if os_mergename=="nary" & regexm(term, "2008|2010|2012")
	replace os_mergename = "mcclanahan"	if os_mergename=="clanahan" & regexm(term, "2008|2010")
	replace os_mergename = "mcmanus"	if os_mergename=="manus" & regexm(term, "2012|2014|2016")
	replace os_mergename = "mcgeoghegan"	if os_mergename=="geoghegan" & regexm(term, "2012|2014")
	replace os_mergename = "mccaherty"	if os_mergename=="caherty" & regexm(term, "2012|2014|2016")
	replace os_mergename = "mckenna"	if os_mergename=="kenna" & regexm(term, "2014")
	replace os_mergename = "mcgaugh"	if os_mergename=="gaugh" & regexm(term, "2014|2016")
	replace os_mergename = "mcdaniel"	if os_mergename=="daniel" & regexm(term, "2016")
	replace os_mergename = "mccreery"	if os_mergename=="creery" & regexm(term, "2016")
	replace os_mergename = "lafaver"	if os_mergename=="faver" & regexm(term, "2014|2016")

	replace os_mergename = "petersbaker"	if os_mergename=="baker" & regexm(term, "2012")	
	replace os_mergename = "kelley"	if os_mergename=="kelley127" & regexm(term, "2013")
	replace os_mergename = "kelly"	if os_mergename=="kelly45" & regexm(term, "2013")
	replace os_mergename = "kelley"	if os_mergename=="kelley126" & regexm(term, "2011")
	replace os_mergename = "kelly"	if os_mergename=="kelly24" & regexm(term, "2011")	
	replace os_mergename = "fisher"	if os_mergename=="fisher125" & regexm(term, "2009")	
	replace os_mergename = "saintonge"	if os_mergename=="onge" & regexm(term, "2007")	
	replace os_mergename = "low"	if os_mergename=="low39" & regexm(term, "2005|2007")	
	replace os_mergename = "lowe"	if os_mergename=="lowe44" & regexm(term, "2005|2007")	
	replace os_mergename = "wright"	if os_mergename=="wright159" & regexm(term, "2007")	
	
	merge m:1  os_mergename term using `tmp'		
		
	// 14 obs not merging
	drop if _merge==1
	drop _merge
	gen floorvotes = regexm(vote, "yes|no")
	replace vote = "1" if vote=="yes"
	replace vote = "-1" if vote=="no"
	replace vote = "0" if regexm(vote, "1")==0
	destring vote, replace
	egen vote_id= group(date id url) , missing
	bys party vote_id : egen party_mean=mean(vote)
	gen party_vote = 0
	replace party_vote = 1 if party_mean>0
	replace party_vote = -1 if party_mean<0
	gen vote_party_line = vote==party_vote
	gen vote_against_party = vote==-party_vote

	save ../data_output/floorvotes_MO_1999_2016.dta, replace	
project , creates(../data_output/floorvotes_MO_1999_2016.dta)	
	
	use ../data_output/floorvotes_MO_1999_2016.dta, clear
	collapse  (mean) vote_party_line vote_against_party (sum)  floorvotes (first) elections_* terms_in_office win TermLengthActual CandName district chamber state party  inoffice year  vtsh, by(CandId term ) //close_floorvotes
	
	save ../data_output/total_floorvotes_MO_1999_2016.dta, replace
project, creates(../data_output/total_floorvotes_MO_1999_2016.dta)	
